﻿#Region "Namespaces"
Imports System
Imports System.Threading
Imports System.Collections.Generic
Imports System.Runtime.InteropServices
Imports System.Text
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports Microsoft.Win32
Imports System.Xml
Imports System.IO
Imports System.Drawing.Graphics
Imports System.ComponentModel
#End Region

Public Class Search_Form

    Private Sub Btn_Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_Search.Click
        'Create SQL Query
        Create_Query()

        'QUERY DATABASE
        'Find Total number of rows fetched
        Dim SQLResult As Object
        Dim summary As Integer
        Dim conn As New SqlConnection(csb.ToString())
        Try
            ESBD_Form.OpenSQLConnection()

            cmd.Connection = conn
            cmd.CommandText = SQLCount

            conn.Open()
            SQLResult = cmd.ExecuteScalar()
            summary = SQLResult
            conn.Close()

            If summary = 0 Then
                MsgBox("There are 0 records that match the specified search parameters.", MsgBoxStyle.Information)
                GoTo Clean_Up
            End If
        Catch ex As Exception
            MsgBox(ex.message)
        End Try

        'Create new Search_Results window each time Search button is clicked.
        SR = New Search_Results 'To allow multiple search results windows

        'Get Data and fill it into datagridview (DGV_SR)
        Dim SampleSource As New DataSet
        Dim TableView As DataView
        ESBD_Form.OpenSQLConnection()
        'Dim conn As New SqlConnection(csb.ToString())

        'Dim SampleCommand As New SqlCommand()
        Dim SampleDataAdapter = New SqlDataAdapter()
        cmd.CommandText = SQLQuery
        cmd.Connection = conn
        conn.Open()
        SampleDataAdapter.SelectCommand = cmd
        SampleDataAdapter.Fill(SampleSource)
        TableView = SampleSource.Tables(0).DefaultView
        conn.Close()

        'Insert data into datagridview
        SR.DGV_Search_Results.DataSource = TableView

        'Get Number of Columns. Cosmetic Modifcations moved to Search_Result Form load.
        ESBD_Form.OpenSQLConnection()
        'Dim conn As New SqlConnection(csb.ToString())
        cmd.Connection = conn
        conn.Open()

        'Get number of columns in the table
        'Dim SQLResult As Object
        cmd.CommandText = "SELECT COUNT(COLUMN_NAME) FROM information_schema.columns where TABLE_NAME='ESBD_Main'"
        SQLResult = cmd.ExecuteScalar
        Number_of_Columns = SQLResult
        conn.Close()

        SR.Show()
Clean_UP:
    End Sub

    Private Sub Create_Query()

        'Init Vars
        'Create variable to store base SQL Query for the count of records retrieved
        SQLCount = "Select count(*) from ESBD_Main"
        SQLQuery = "Select * from ESBD_Main"
        Dim AtchFile As String = ""
        Dim BomRev As String = ""
        Dim BuildIns As String = ""
        Dim BuildQty As String = ""
        Dim BuildType As String = ""
        Dim Comments As String = ""
        Dim CompDate As String = ""
        Dim Customer As String = ""
        Dim JobID As String = ""
        Dim LedSpec As String = ""
        Dim PcbAssm As String = ""
        Dim PcbManu As String = ""
        Dim PcbRev As String = ""
        Dim Pcbznn As String = ""
        Dim ProjName As String = ""
        Dim ReqDate As String = ""
        Dim ShipDate As String = ""
        Dim Status As String = ""

        'Determine if all search text boxes are blank
        If (cmb_AttachFile.Text = "" And TB_BOMRev.Text = "" And TB_BuildIns.Text = "" And TB_BuildQty.Text = "" And TB_BuildType.Text = "" And TB_Comments.Text = "" And TB_CompDate.Text = "" And cmb_Customer.Text = "" And TB_JobID.Text = "" And TB_LEDSpec.Text = "" And cmb_PCBAssm.Text = "" And cmb_PCBManu.Text = "" And TB_PCBRev.Text = "" And TB_PCBZNN.Text = "" And cmb_ProjName.Text = "" And TB_ReqDate.Text = "" And TB_ShipDate.Text = "" And cmb_Status.Text = "") Then
            SQLQuery = SQLQuery
        Else
            '------------------------------------------------------------------------
            If cmb_AttachFile.Text = "" Then
                AtchFile = ""
            Else
                'If this is last search field entered then
                If (TB_BOMRev.Text = "" And TB_BuildIns.Text = "" And TB_BuildQty.Text = "" And TB_BuildType.Text = "" And TB_Comments.Text = "" And TB_CompDate.Text = "" And cmb_Customer.Text = "" And TB_JobID.Text = "" And TB_LEDSpec.Text = "" And cmb_PCBAssm.Text = "" And cmb_PCBManu.Text = "" And TB_PCBRev.Text = "" And TB_PCBZNN.Text = "" And cmb_ProjName.Text = "" And TB_ReqDate.Text = "" And TB_ShipDate.Text = "" And cmb_Status.Text = "") Then
                    AtchFile = "Shipping_Record = '" + cmb_AttachFile.Text + "'"
                Else
                    AtchFile = "Shipping_Record = '" + cmb_AttachFile.Text + "' And "
                End If
            End If
            '------------------------------------------------------------------------
            If TB_BOMRev.Text = "" Then
                BomRev = ""
            Else
                'If this is last search field entered then
                If (TB_BuildIns.Text = "" And TB_BuildQty.Text = "" And TB_BuildType.Text = "" And TB_Comments.Text = "" And TB_CompDate.Text = "" And cmb_Customer.Text = "" And TB_JobID.Text = "" And TB_LEDSpec.Text = "" And cmb_PCBAssm.Text = "" And cmb_PCBManu.Text = "" And TB_PCBRev.Text = "" And TB_PCBZNN.Text = "" And cmb_ProjName.Text = "" And TB_ReqDate.Text = "" And TB_ShipDate.Text = "" And cmb_Status.Text = "") Then
                    BomRev = "BOM_Rev = '" + TB_BOMRev.Text + "'"
                Else
                    BomRev = "BOM_Rev = '" + TB_BOMRev.Text + "' And "
                End If
            End If
            '------------------------------------------------------------------------
            If TB_BuildIns.Text = "" Then
                BuildIns = ""
            Else
                'If this is last search field entered then
                If (TB_BuildQty.Text = "" And TB_BuildType.Text = "" And TB_Comments.Text = "" And TB_CompDate.Text = "" And cmb_Customer.Text = "" And TB_JobID.Text = "" And TB_LEDSpec.Text = "" And cmb_PCBAssm.Text = "" And cmb_PCBManu.Text = "" And TB_PCBRev.Text = "" And TB_PCBZNN.Text = "" And cmb_ProjName.Text = "" And TB_ReqDate.Text = "" And TB_ShipDate.Text = "" And cmb_Status.Text = "") Then
                    BuildIns = "Special_Build_Instruction like '%" + TB_BuildIns.Text + "%'"
                Else
                    BuildIns = "Special_Build_Instruction like '%" + TB_BuildIns.Text + "%' And "
                End If
            End If
            '------------------------------------------------------------------------
            If TB_BuildQty.Text = "" Then
                BuildQty = ""
            Else
                'If this is last search field entered then
                If (TB_BuildType.Text = "" And TB_Comments.Text = "" And TB_CompDate.Text = "" And cmb_Customer.Text = "" And TB_JobID.Text = "" And TB_LEDSpec.Text = "" And cmb_PCBAssm.Text = "" And cmb_PCBManu.Text = "" And TB_PCBRev.Text = "" And TB_PCBZNN.Text = "" And cmb_ProjName.Text = "" And TB_ReqDate.Text = "" And TB_ShipDate.Text = "" And cmb_Status.Text = "") Then
                    BuildQty = "Build_Quantity = '" + TB_BuildQty.Text + "'"
                Else
                    BuildQty = "Build_Quantity = '" + TB_BuildQty.Text + "' And "
                End If
            End If
            '------------------------------------------------------------------------
            If TB_BuildType.Text = "" Then
                BuildType = ""
            Else
                'If this is last search field entered then
                If (TB_Comments.Text = "" And TB_CompDate.Text = "" And cmb_Customer.Text = "" And TB_JobID.Text = "" And TB_LEDSpec.Text = "" And cmb_PCBAssm.Text = "" And cmb_PCBManu.Text = "" And TB_PCBRev.Text = "" And TB_PCBZNN.Text = "" And cmb_ProjName.Text = "" And TB_ReqDate.Text = "" And TB_ShipDate.Text = "" And cmb_Status.Text = "") Then
                    BuildType = "Build_Type = '" + TB_BuildType.Text + "'"
                Else
                    BuildType = "Build_Type = '" + TB_BuildType.Text + "' And "
                End If
            End If
            '------------------------------------------------------------------------
            If TB_Comments.Text = "" Then
                Comments = ""
            Else
                'If this is last search field entered then
                If (TB_CompDate.Text = "" And cmb_Customer.Text = "" And TB_JobID.Text = "" And TB_LEDSpec.Text = "" And cmb_PCBAssm.Text = "" And cmb_PCBManu.Text = "" And TB_PCBRev.Text = "" And TB_PCBZNN.Text = "" And cmb_ProjName.Text = "" And TB_ReqDate.Text = "" And TB_ShipDate.Text = "" And cmb_Status.Text = "") Then
                    Comments = "Comments like '%" + TB_Comments.Text + "%'"
                Else
                    Comments = "Comments like '%" + TB_Comments.Text + "%' And "
                End If
            End If
            '------------------------------------------------------------------------
            If TB_CompDate.Text = "" Then
                CompDate = ""
            Else
                'If this is last search field entered then
                If (cmb_Customer.Text = "" And TB_JobID.Text = "" And TB_LEDSpec.Text = "" And cmb_PCBAssm.Text = "" And cmb_PCBManu.Text = "" And TB_PCBRev.Text = "" And TB_PCBZNN.Text = "" And cmb_ProjName.Text = "" And TB_ReqDate.Text = "" And TB_ShipDate.Text = "" And cmb_Status.Text = "") Then
                    CompDate = "Completion_Date = '" + TB_CompDate.Text + "'"
                Else
                    CompDate = "Completion_Date = '" + TB_CompDate.Text + "' And "
                End If
            End If
            '------------------------------------------------------------------------
            If cmb_Customer.Text = "" Then
                Customer = ""
            Else
                'If this is last search field entered then
                If (TB_JobID.Text = "" And TB_LEDSpec.Text = "" And cmb_PCBAssm.Text = "" And cmb_PCBManu.Text = "" And TB_PCBRev.Text = "" And TB_PCBZNN.Text = "" And cmb_ProjName.Text = "" And TB_ReqDate.Text = "" And TB_ShipDate.Text = "" And cmb_Status.Text = "") Then
                    Customer = "Customer = '" + cmb_Customer.Text + "'"
                Else
                    Customer = "Customer = '" + cmb_Customer.Text + "' And "
                End If
            End If
            '------------------------------------------------------------------------
            If TB_JobID.Text = "" Then
                JobID = ""
            Else
                'If this is last search field entered then
                If (TB_LEDSpec.Text = "" And cmb_PCBAssm.Text = "" And cmb_PCBManu.Text = "" And TB_PCBRev.Text = "" And TB_PCBZNN.Text = "" And cmb_ProjName.Text = "" And TB_ReqDate.Text = "" And TB_ShipDate.Text = "" And cmb_Status.Text = "") Then
                    JobID = "Job_ID = '" + TB_JobID.Text + "'"
                Else
                    JobID = "Job_ID = '" + TB_JobID.Text + "' And "
                End If
            End If
            '------------------------------------------------------------------------
            If TB_LEDSpec.Text = "" Then
                LedSpec = ""
            Else
                'If this is last search field entered then
                If (cmb_PCBAssm.Text = "" And cmb_PCBManu.Text = "" And TB_PCBRev.Text = "" And TB_PCBZNN.Text = "" And cmb_ProjName.Text = "" And TB_ReqDate.Text = "" And TB_ShipDate.Text = "" And cmb_Status.Text = "") Then
                    LedSpec = "LED_Specification like '%" + TB_LEDSpec.Text + "%'"
                Else
                    LedSpec = "LED_Specification like '%" + TB_LEDSpec.Text + "%' And "
                End If
            End If
            '------------------------------------------------------------------------
            If cmb_PCBAssm.Text = "" Then
                PcbAssm = ""
            Else
                'If this is last search field entered then
                If (cmb_PCBManu.Text = "" And TB_PCBRev.Text = "" And TB_PCBZNN.Text = "" And cmb_ProjName.Text = "" And TB_ReqDate.Text = "" And TB_ShipDate.Text = "" And cmb_Status.Text = "") Then
                    PcbAssm = "PCB_Assembler = '" + cmb_PCBAssm.Text + "'"
                Else
                    PcbAssm = "PCB_Assembler = '" + cmb_PCBAssm.Text + "' And "
                End If
            End If
            '------------------------------------------------------------------------
            If cmb_PCBManu.Text = "" Then
                PcbManu = ""
            Else
                'If this is last search field entered then
                If (TB_PCBRev.Text = "" And TB_PCBZNN.Text = "" And cmb_ProjName.Text = "" And TB_ReqDate.Text = "" And TB_ShipDate.Text = "" And cmb_Status.Text = "") Then
                    PcbManu = "PCB_Manufacturer = '" + cmb_PCBManu.Text + "'"
                Else
                    PcbManu = "PCB_Manufacturer = '" + cmb_PCBManu.Text + "' And "
                End If
            End If
            '------------------------------------------------------------------------
            If TB_PCBRev.Text = "" Then
                PcbRev = ""
            Else
                'If this is last search field entered then
                If (TB_PCBZNN.Text = "" And cmb_ProjName.Text = "" And TB_ReqDate.Text = "" And TB_ShipDate.Text = "" And cmb_Status.Text = "") Then
                    PcbRev = "PCB_Rev = '" + TB_PCBRev.Text + "'"
                Else
                    PcbRev = "PCB_Rev = '" + TB_PCBRev.Text + "' And "
                End If
            End If
            '------------------------------------------------------------------------
            If TB_PCBZNN.Text = "" Then
                Pcbznn = ""
            Else
                'If this is last search field entered then
                If (cmb_ProjName.Text = "" And TB_ReqDate.Text = "" And TB_ShipDate.Text = "" And cmb_Status.Text = "") Then
                    Pcbznn = "PCB_ZNN = '" + TB_PCBZNN.Text + "'"
                Else
                    Pcbznn = "PCB_ZNN = '" + TB_PCBZNN.Text + "' And "
                End If
            End If
            '------------------------------------------------------------------------
            If cmb_ProjName.Text = "" Then
                ProjName = ""
            Else
                'If this is last search field entered then
                If (TB_ReqDate.Text = "" And TB_ShipDate.Text = "" And cmb_Status.Text = "") Then
                    ProjName = "Project_Name = '" + cmb_ProjName.Text + "'"
                Else
                    ProjName = "Project_Name = '" + cmb_ProjName.Text + "' And "
                End If
            End If
            '------------------------------------------------------------------------
            If TB_ReqDate.Text = "" Then
                ReqDate = ""
            Else
                'If this is last search field entered then
                If (TB_ShipDate.Text = "" And cmb_Status.Text = "") Then
                    ReqDate = "Requested_Date = '" + TB_ReqDate.Text + "'"
                Else
                    ReqDate = "Requested_Date = '" + TB_ReqDate.Text + "' And "
                End If
            End If
            '------------------------------------------------------------------------
            If TB_ShipDate.Text = "" Then
                ShipDate = ""
            Else
                'If this is last search field entered then
                If (cmb_Status.Text = "") Then
                    ShipDate = "Ship_Date = '" + TB_ShipDate.Text + "'"
                Else
                    ShipDate = "Ship_Date = '" + TB_ShipDate.Text + "' And "
                End If
            End If
            '------------------------------------------------------------------------
            If cmb_Status.Text = "" Then
                Status = ""
            Else
                Status = "Status = '" + cmb_Status.Text + "'"
            End If
            '------------------------------------------------------------------------
            '========================================================================
            '------------------------------------------------------------------------
            'Build Query
            SQLQuery = ((SQLQuery) + (" Where " + AtchFile + BomRev + BuildIns + BuildQty + BuildType + Comments + CompDate + Customer + JobID + LedSpec + PcbAssm + PcbManu + PcbRev + Pcbznn + ProjName + ReqDate + ShipDate + Status))

            'Build Query for Counting number records retrieved
            SQLCount = ((SQLCount) + (" Where " + AtchFile + BomRev + BuildIns + BuildQty + BuildType + Comments + CompDate + Customer + JobID + LedSpec + PcbAssm + PcbManu + PcbRev + Pcbznn + ProjName + ReqDate + ShipDate + Status))

        End If
    End Sub

    Private Sub Search_Form_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Populate Comboboxes
        Try
            'Clear ComboBoxes
            cmb_Customer.Items.Clear()
            cmb_AttachFile.Items.Clear()
            cmb_PCBAssm.Items.Clear()
            cmb_PCBManu.Items.Clear()
            cmb_ProjName.Items.Clear()
            cmb_Status.Items.Clear()

            'Refresh ComboBox
            ESBD_Form.OpenSQLConnection()
            Dim connection As New SqlConnection(csb.ToString())
            cmd.Connection = connection
            connection.Open()

            'Use reader to popluate comboboxes
            cmd.CommandText = "Select ""Name"" from ""CUST_TB"""
            dr = cmd.ExecuteReader()
            Do While dr.Read()
                cmb_Customer.Items.Add(dr.GetString(0))
            Loop
            dr.Close()

            cmd.CommandText = "Select ""Filename"" from Attachments_TB"
            dr = cmd.ExecuteReader()
            Do While dr.Read()
                cmb_AttachFile.Items.Add(dr.GetString(0))
            Loop
            dr.Close()

            cmd.CommandText = "Select ""Name"" from PCBAssm_TB"
            dr = cmd.ExecuteReader()
            Do While dr.Read()
                cmb_PCBAssm.Items.Add(dr.GetString(0))
            Loop
            dr.Close()

            cmd.CommandText = "Select ""Name"" from PCBManu_TB"
            dr = cmd.ExecuteReader()
            Do While dr.Read()
                cmb_PCBManu.Items.Add(dr.GetString(0))
            Loop
            dr.Close()

            cmd.CommandText = "Select ""PJ_Name"" from Project_TB"
            dr = cmd.ExecuteReader()
            Do While dr.Read()
                cmb_ProjName.Items.Add(dr.GetString(0))
            Loop
            dr.Close()

            cmd.CommandText = "Select ""Name"" from Status_TB"
            dr = cmd.ExecuteReader()
            Do While dr.Read()
                cmb_Status.Items.Add(dr.GetString(0))
            Loop
            dr.Close()
            connection.Close()

            'Sort the Combobox
            cmb_Customer.Sorted = True
            cmb_AttachFile.Sorted = True
            cmb_PCBAssm.Sorted = True
            cmb_PCBManu.Sorted = True
            cmb_ProjName.Sorted = True
            cmb_Status.Sorted = True
        Catch ex As Exception
            Dim connection As New SqlConnection(csb.ToString())
            connection.Close()
            MsgBox(ex.Message)
        End Try
    End Sub
#Region "ComboBoxes Click Events"
    Private Sub cmb_CustName_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_Customer.Click
        cmb_Customer.DroppedDown = True
    End Sub

    Private Sub cmb_AtchFile_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_AttachFile.Click
        cmb_AttachFile.DroppedDown = True
    End Sub

    Private Sub cmb_PCBAssm_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_PCBAssm.Click
        cmb_PCBAssm.DroppedDown = True
    End Sub

    Private Sub cmb_PCBManu_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_PCBManu.Click
        cmb_PCBManu.DroppedDown = True
    End Sub

    Private Sub cmb_ProjName_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_ProjName.Click
        cmb_ProjName.DroppedDown = True
    End Sub

    Private Sub cmb_Status_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_Status.Click
        cmb_Status.DroppedDown = True
    End Sub

#End Region

End Class